package zm.irc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;



import org.apache.log4j.Logger;

import zm.irc.client.IrcClient;
import zm.irc.connpool.DbConnectionPool;
import zm.irc.dto.ChannelRankingInfo;
import zm.irc.dto.ChatMessageHistory;
import zm.irc.ioc.annotation.AutoWired;
import zm.irc.ioc.annotation.Service;
import zm.irc.message.receive.IrcReceiveChatMessage;


@Service
public class MessageDao {
    Logger log = Logger.getLogger(MessageDao.class);

    @AutoWired
    private DbConnectionPool dbConnectionPool;
    
    public void saveMessage(IrcReceiveChatMessage msg){
        Connection conn = null;
        PreparedStatement ps = null;
        try {
                conn = dbConnectionPool.getConnection();
                if(conn == null){
                    return;
                }
                IrcClient ircClient = msg.getIrcClient();
                ps = conn.prepareCall("insert into IRC_LOG (msg_type," +
                        "from_name," +
                        "from_ip," +
                        "content," +
                        "channel," +
                        "host) values(?,?,?,?,?,?)");
                ps.setString(1,"chat_msg");
                ps.setString(2,msg.getFromName());
                ps.setString(3,msg.getFromIp());
                ps.setString(4,msg.getMessageBody());
                ps.setString(5,msg.getChannel());
                ps.setString(6, ircClient.getServer() + ":" +ircClient.getPort());
                ps.execute();

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DbConnectionPool.close(ps);
            DbConnectionPool.close(conn);
        }
    }


    public List<ChannelRankingInfo> queryTop(int topCount){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<ChannelRankingInfo> top10= new ArrayList<>();
        try {
                conn = dbConnectionPool.getConnection();
                if(conn == null){
                    return Collections.emptyList();
                }
                ps = conn.prepareCall("select channel," +
                        "count(1)," +
                        "min(time)," +
                        "max(time)" +
                        "from IRC_LOG " +
                        "group by channel " +
                        "limit 0,?");
                ps.setInt(1, topCount);
                rs = ps.executeQuery();

                while(rs.next()){
                    ChannelRankingInfo info = new ChannelRankingInfo();
                    info.setChannel(rs.getString(1));
                    info.setMsgCount(rs.getInt(2));

                    Timestamp startAt = (Timestamp)rs.getObject(3);
                    Timestamp endAt = (Timestamp)rs.getObject(4);

                    info.setStartAt(LocalDateTime.ofInstant(Instant.ofEpochMilli(startAt.getTime()), ZoneId.systemDefault()));
                    info.setEndAt(LocalDateTime.ofInstant(Instant.ofEpochMilli(endAt.getTime()), ZoneId.systemDefault()));
                    top10.add(info);
                }


        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DbConnectionPool.close(rs);
            DbConnectionPool.close(ps);
            DbConnectionPool.close(conn);
        }
        return top10;
    }

    /**
     * query chat history of channel of today.(pagination by pageNumber and pageSize)
     * @param channel
     * @param pageNumber
     * @param pageSize
     * @return
     */
    public List<ChatMessageHistory> queryHistory(String channel,int pageNumber,int pageSize){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<ChatMessageHistory> histories = new ArrayList<>();
        try {
                int startLineNumber = (pageNumber - 1) * pageSize;
                conn = dbConnectionPool.getConnection();
                ps = conn.prepareCall("select * " +
                        "from IRC_LOG " +
                        "where channel = ? "+
                        "and TO_DAYS(`time`) =TO_DAYS(NOW()) " +
                        "order by time desc " +
                        "limit ?,?");
                ps.setString(1, channel);
                ps.setInt(2, startLineNumber);
                ps.setInt(3, pageSize);
                rs = ps.executeQuery();

                while(rs.next()){
                    ChatMessageHistory history = new ChatMessageHistory();
                    Timestamp time = rs.getTimestamp("time");
                    String fromName = rs.getString("from_name");
                    String content = rs.getString("content");
                    history.setChannel(channel);
                    history.setContent(content);
                    history.setFromName(fromName);

                    if( time != null){
                        history.setTime(time.toLocalDateTime());
                    }
                    
                    histories.add(history);
                }


        }catch (Exception e){
            e.printStackTrace();
        }finally {
            DbConnectionPool.close(rs);
            DbConnectionPool.close(ps);
            DbConnectionPool.close(conn);
        }
        return histories;
    }
}
